﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace TVGuide.DataAccess
{
    public class DbAccessHelper
    {
        private SqlConnection GetConnection()
        {
          //  SqlConnection con = new SqlConnection(@"data source=ARAM-COMP183\SQLEXPRESS;initial catalog=noramirkh;Integrated Security=true");
            SqlConnection con = new SqlConnection(@"Data Source=noramirkh.db.7733219.hostedresource.com;Initial Catalog=noramirkh;persist security info=True;user id=noramirkh;password=AVabBe_5a4"); 
            return con;
        }

        public DataTable GetAllCategory(out int error_code)
        {
            error_code = 0;
            DataTable dt = new DataTable();
            SqlConnection con = GetConnection();
            try
            {
                con.Open();
            }
            catch
            {
                error_code = 1;
                return dt;
            }
            SqlDataAdapter dAdapter = new SqlDataAdapter("dbo.sp_Client_GetAllCategory", con);
            dAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                dAdapter.Fill(dt);
            }
            catch
            {
                return dt;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return dt;
        }

        public DataTable GetAllChannels(out int error_code)
        {
            error_code = 0;
            DataTable dt = new DataTable();
            SqlConnection con = GetConnection();
            try
            {
                con.Open();
            }
            catch
            {
                error_code = 1;
                return dt;
            }
            SqlDataAdapter dAdapter = new SqlDataAdapter("dbo.sp_Client_GetAllChannels", con);
            dAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                dAdapter.Fill(dt);
            }
            catch
            {
                return dt;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return dt;
        }

        public DataTable GetProgramsWithSpecChannel(out int error_code, int chid, DateTime start_date, DateTime end_date)
        {
            error_code = 0;
            DataTable dt = new DataTable();
            SqlConnection con = GetConnection();
            try
            {
                con.Open();
            }
            catch
            {
                error_code = 1;
                return dt;
            }
            SqlDataAdapter dAdapter = new SqlDataAdapter("dbo.sp_Client_GetProgramsWithSpecifiedDate", con);
            dAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter ChanID = new SqlParameter();
            ChanID.ParameterName = "@ChannelID";
            ChanID.SqlDbType = SqlDbType.Int;
            ChanID.Value = chid;

            SqlParameter St_date = new SqlParameter();
            St_date.ParameterName = "@StartDate";
            St_date.SqlDbType = SqlDbType.DateTime;
            St_date.Value = start_date;

            SqlParameter End_date = new SqlParameter();
            End_date.ParameterName = "@EndDate";
            End_date.SqlDbType = SqlDbType.DateTime;
            End_date.Value = end_date;

            

            try
            {
                dAdapter.SelectCommand.Parameters.Add(ChanID);
                dAdapter.SelectCommand.Parameters.Add(St_date);
                dAdapter.SelectCommand.Parameters.Add(End_date);
                dAdapter.Fill(dt);
            }
            catch
            {
                return dt;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return dt;
        }

        public DataTable GetProgramsWithChannelSpecDate(out int error_code, DateTime start_date, DateTime end_date)
        {
            error_code = 0;
            DataTable dt = new DataTable();
            SqlConnection con = GetConnection();
            try
            {
                con.Open();
            }
            catch
            {
                error_code = 1;
                return dt;
            }
            SqlDataAdapter dAdapter = new SqlDataAdapter("dbo.sp_Client_GetProgramsWithChannelsSpecifiedDate_test", con);
            dAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter St_date = new SqlParameter();
            St_date.ParameterName = "@StartDate";
            St_date.SqlDbType = SqlDbType.DateTime;
            St_date.Value = start_date;

            SqlParameter End_date = new SqlParameter();
            End_date.ParameterName = "@EndDate";
            End_date.SqlDbType = SqlDbType.DateTime;
            End_date.Value = end_date;

            try
            { 
                dAdapter.SelectCommand.Parameters.Add(St_date);
                dAdapter.SelectCommand.Parameters.Add(End_date);
                dAdapter.Fill(dt);
            }
            catch
            {
                return dt;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return dt;
        }

        public DataTable GetAllProgramsWithSpecDate(out int error_code, DateTime start_date, DateTime end_date)
        {
            error_code = 0;
            DataTable dt = new DataTable();
            SqlConnection con = GetConnection();
            try
            {
                con.Open();
            }
            catch
            {
                error_code = 1;
                return dt;
            }
            SqlDataAdapter dAdapter = new SqlDataAdapter("dbo.sp_Client_GetAllProgramsWithSpecifiedDate", con);
            dAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter St_date = new SqlParameter();
            St_date.ParameterName = "@StartDate";
            St_date.SqlDbType = SqlDbType.DateTime;
            St_date.Value = start_date;

            SqlParameter End_date = new SqlParameter();
            End_date.ParameterName = "@EndDate";
            End_date.SqlDbType = SqlDbType.DateTime;
            End_date.Value = end_date;

            try
            {
                dAdapter.SelectCommand.Parameters.Add(St_date);
                dAdapter.SelectCommand.Parameters.Add(End_date);
                dAdapter.Fill(dt);
            }
            catch
            {
                return dt;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return dt;
        }

    }
}
